package com.caopeng.javaee.service;

import com.caopeng.javaee.entity.student;
import com.caopeng.javaee.util.ExcelImportUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import org.apache.commons.lang3.StringUtils;
import java.io.File;
import java.io.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @program: javaee
 * @description: excel解析插入数据库
 * @author: caopeng
 * @create: 2018-12-10 15:41
 **/
@Service
public class ExcelService {
    @Autowired
    CourseStudentService courseStudentService;
    @Autowired
    StudentService studentService;
    public String batchImport(String fileName, MultipartFile mfile,String course_id){

        File uploadDir = new  File("D:\\fileupload");
        //创建一个目录 （它的路径名由当前 File 对象指定，包括任一必须的父路径。）
        if (!uploadDir.exists()) uploadDir.mkdirs();
        //新建一个文件
        File tempFile = new File("D:\\fileupload\\" + new Date().getTime() + ".xlsx");
        //初始化输入流
        InputStream is = null;
        try{
            //将上传的文件写入新建的文件中
            mfile.transferTo(tempFile);

            //根据新建的文件实例化输入流
            is = new FileInputStream(tempFile);

            //根据版本选择创建Workbook的方式
            Workbook wb = null;
            //根据文件名判断文件是2003版本还是2007版本
            if(ExcelImportUtils.isExcel2007(fileName)){
                wb = new XSSFWorkbook(is);
            }else{
                wb = new HSSFWorkbook(is);
            }
            //根据excel里面的内容读取知识库信息
            return readExcelValue(wb,tempFile,course_id);
        }catch(Exception e){
            e.printStackTrace();
        } finally{
            if(is !=null)
            {
                try{
                    is.close();
                }catch(IOException e){
                    is = null;
                    e.printStackTrace();
                }
            }
        }
        return "导入出错！请检查数据格式！";
    }
    /**
     * 解析Excel里面的数据
     * @param wb
     * @return
     */
    private String readExcelValue(Workbook wb,File tempFile,String course_id){

        //错误信息接收器
        String errorMsg = "";
        //得到第一个sheet
        Sheet sheet=wb.getSheetAt(0);
        //得到Excel的行数          row行数
        int totalRows=sheet.getPhysicalNumberOfRows();
        //总列数
        int totalCells = 0;
        //得到Excel的列数(前提是有行数)，从第二行算起
        if(totalRows>=2 && sheet.getRow(1) != null){
            totalCells=sheet.getRow(1).getPhysicalNumberOfCells();
        }
        List<student> studnetlist=new ArrayList<student>();
        student tempstudent;
        System.out.println("tatalcells"+totalCells+"totalrows"+totalRows);
        String br = "   ";

        //循环Excel行数,从第二行开始。标题不入库
        for(int r=1;r<totalRows;r++){
            String rowMessage = "";
            Row row = sheet.getRow(r);
            if (row == null){
                errorMsg += br+"第"+(r+1)+"行数据有问题，请仔细检查！";
                continue;
            }
            tempstudent = new student();

            String studentname = "";
            String studentaccount = "";

            //循环Excel的列
            for(int c = 0; c <totalCells; c++){
                Cell cell = row.getCell(c);
                if (null != cell){
                    row.getCell(c).setCellType(Cell.CELL_TYPE_STRING);
                    if(c==0){
                        studentname = cell.getStringCellValue();
                        if(StringUtils.isEmpty(studentname)){
                            rowMessage += "姓名不能为空；";
                        }else if(studentname.length()>10){
                            rowMessage += "姓名不能超过10个字；";
                        }
                        System.out.println("第一列的字符串是"+studentname);
                        tempstudent.setName(studentname);
                    }else if(c==1){
                        studentaccount = cell.getStringCellValue();
                        if(StringUtils.isEmpty(studentaccount)){
                            rowMessage += "答案不能为空；";
                        }else if(studentaccount.length()>20){
                            rowMessage += "账号不能超过20个字；";
                        }
                        tempstudent.setAccount(studentaccount);
                        System.out.println("第二列的字符串是"+studentaccount);
                    }
                }else{
                    rowMessage += "第"+(c+1)+"列数据有问题，请仔细检查；";
                }
            }
            //拼接每行的错误提示
            if(!StringUtils.isEmpty(rowMessage)){
                errorMsg += br+"第"+(r+1)+"行，"+rowMessage;
            }else{
                    studnetlist.add(tempstudent);
            }
        }

        //删除上传的临时文件
        if(tempFile.exists()){
            tempFile.delete();
        }

        //全部验证通过才导入到数据库
        if(StringUtils.isEmpty(errorMsg)){
//            for(UserKnowledgeBase userKnowledgeBase : userKnowledgeBaseList){
////                this.saveUserKnowledge(userKnowledgeBase);
////            }
////            errorMsg = "导入成功，共"+userKnowledgeBaseList.size()+"条数据！";
            for(student thestudent:studnetlist)
            {
                student mystudent=studentService.SfindByAccountAndName(thestudent.getAccount(),thestudent.getName());
                System.out.println("这个学生的id为"+mystudent.getId());
                if(null==mystudent)
                {
                    continue;
                }
                courseStudentService.CourseAddStudent(Integer.parseInt(course_id),mystudent.getId());
            }
        }
        return errorMsg;
    }

}

